/**
 * 
 */
package org.chen.preprocess;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.velocity.Template;
import org.apache.velocity.VelocityContext;
import org.apache.velocity.app.VelocityEngine;
import org.apache.velocity.io.VelocityWriter;
import org.apache.velocity.runtime.RuntimeConstants;

/**
 * 根据mysql生成javabean
 * 
 * @author administrator
 * 
 */
public class JavaBeanCreator {
	private final static Log log = LogFactory.getLog(JavaBeanCreator.class);
	private final static String JAVABEAN_FOLDER = "test/com/zoiz/base/model";
	private final static String SQL_COLUMN_COMMENT = "select table_name,column_name,column_comment from information_schema.columns where table_schema=?";
	private final static String SQL_TABLE_COMMENT = "select table_name,table_comment  from information_schema.tables where table_schema=?";
	private final static String EXTEND_CLASS = "com.zoiz.dbutils.Model";
	static VelocityEngine ve;
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	private static List<Object[]> getAllTableName(String schema)
			throws SQLException {
		Connection conn = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306", "root", "root");
		QueryRunner qRunner = new QueryRunner();
		List<Object[]> tableNameList = qRunner
				.query(conn,
						"SELECT table_name from information_schema.TABLES where table_schema=?",
						new ArrayListHandler(), schema);
		return tableNameList;
	}

	private static Map<String, String> getAllColumnComment(String schema)
			throws SQLException {
		Connection conn = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306", "root", "root");
		QueryRunner qRunner = new QueryRunner();
		Map<String, String> result = new HashMap<String, String>();
		List<Object[]> List = qRunner.query(conn, SQL_COLUMN_COMMENT,
				new ArrayListHandler(), schema);
		for (Object[] column : List) {
			result.put(
					(column[0].toString() + "-" + column[1].toString())
							.toLowerCase(),
					StringUtils.isEmpty(column[2].toString()) ? column[1]
							.toString() : column[2].toString());
		}
		return result;
	}

	private static Map<String, String> getAllTableComment(String schema)
			throws SQLException {
		Connection conn = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306", "root", "root");
		QueryRunner qRunner = new QueryRunner();
		Map<String, String> result = new HashMap<String, String>();
		List<Object[]> List = qRunner.query(conn, SQL_TABLE_COMMENT,
				new ArrayListHandler(), schema);
		for (Object[] column : List) {
			result.put(column[0].toString().toLowerCase(), StringUtils
					.isEmpty(column[1].toString()) ? column[0].toString()
					: column[1].toString());
		}
		return result;
	}

	/**
	 * 生存java文件
	 * 
	 * @param filepath
	 * @param schema
	 * @param tpl
	 * @throws SQLException
	 * @throws IOException
	 */
	private static void generateJavaCode(String filepath, String schema,
			Template tpl) throws SQLException, IOException {
		List<Object[]> allTableNamesList = getAllTableName(schema);
		Map<String, String> tablecomment = getAllTableComment(schema);
		Map<String, String> columncomment = getAllColumnComment(schema);

		for (Object[] columns : allTableNamesList) {
			String className = columns[0].toString().substring(0, 1)
					.toUpperCase()
					+ columns[0].toString().substring(1);
			String packageName = JAVABEAN_FOLDER.substring(
					JAVABEAN_FOLDER.indexOf("/") + 1).replaceAll("/", ".");
			FileWriter writer = createFile(filepath, className + ".java");
			VelocityContext context = new VelocityContext();
			context.put("package", packageName);
			context.put("classname", className);
			context.put("comment", tablecomment.get(className.toLowerCase()));
			Field[] fields = getFields(schema, className, columncomment);
			context.put("fields", fields);
			context.put("creator", JavaBeanCreator.class);
			VelocityWriter vw = new VelocityWriter(writer);
			try {
				tpl.merge(context, vw);
				vw.flush();
			} finally {
				vw.recycle(null);
			}

		}
	}

	/**
	 * 获取所有字段
	 * 
	 * @param schema
	 * @param table
	 * @param ref
	 * @return
	 * @throws SQLException
	 */
	private static Field[] getFields(final String schema,
			final String table, final Map<String, String> ref)
			throws SQLException {
		Connection conn = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/" + schema, "root", "root");
		QueryRunner qRunner = new QueryRunner();
		List<Field> tableNameList = (List<Field>) qRunner.query(conn,
				"SELECT * from " + table.toLowerCase() + " where 1=2",
				new ResultSetHandler<List<Field>>() {
					@Override
					public List<Field> handle(ResultSet rs) throws SQLException {
						ResultSetMetaData metaData = rs.getMetaData();
						List<Field> result = new ArrayList<Field>();
						// 获取表单的列数
						int col = metaData.getColumnCount();
						for (int i = 1; i <= col; i++) {
							result.add(new Field(metaData.getColumnName(i),
									metaData.getColumnClassName(i), ref
											.get((table + "-" + metaData
													.getColumnName(i))
													.toLowerCase()), metaData
											.isAutoIncrement(i)));
						}
						return result;
					}
				});
		Field[] fields=tableNameList.toArray(new Field[tableNameList.size()]);
		Arrays.sort(fields,new Comparator<Field>() {
			@Override
			public int compare(Field arg0, Field arg1) {
				return arg0.isAutoIncrement()?-1:1;
			}
		});
		return fields;
	}

	static FileWriter createFile(String path, String fileName)
			throws IOException {
		File f = new File(path, fileName);
		if (!f.exists()) {
			f.createNewFile();
		}
		FileWriter writer = new FileWriter(f);
		return writer;
	}

	public static void main(String[] args) throws Exception {
		String classPathString = JavaBeanCreator.class.getResource("/")
				.toString();
		String base = classPathString.substring(0,
				classPathString.indexOf("web"));
		if (base.startsWith("file:"))
			base = base.substring(5);
		ve = new VelocityEngine();
		ve.setProperty(RuntimeConstants.RESOURCE_LOADER, "file");
		ve.setProperty(RuntimeConstants.FILE_RESOURCE_LOADER_PATH, base
				+ "preprocess/org/chen/preprocess/");
		ve.setProperty("directive.foreach.counter.name", "velocityCount");
		ve.setProperty("directive.foreach.counter.initial.value", 1);
		ve.init();
		Template t = ve.getTemplate("javabean.vm");
		generateJavaCode(base + JAVABEAN_FOLDER, "mycms", t);
		log.info("done");

	}

	public static String getExtendClass() {
		return EXTEND_CLASS;
	}
	public static String upper(String s){
		return s.toUpperCase();
	}
	public static String lower(String s){
		return s.toLowerCase();
	}
	
}
